﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace ClassLibrary
{
    public partial class DBHandler
    {
        
        #region Database Connection
        public static SqlConnection GetConnection()
        {
            return new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString);
        }
        #endregion

        #region add parameters
        public static void AddParameter(ref SqlCommand cmd, string sParamName, string sValue)
        {
            SqlParameter param = new SqlParameter(sParamName, sValue);
            param.DbType = DbType.String;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);
        }
        public static void AddParameter(ref SqlCommand cmd, string sParamName, Guid gValue)
        {
            SqlParameter param = new SqlParameter(sParamName, gValue);
            param.DbType = DbType.Guid;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);
        }
        public static void AddParameter(ref SqlCommand cmd, string sParamName, int nValue)
        {
            SqlParameter param = new SqlParameter(sParamName, nValue);
            param.DbType = DbType.Int32;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);
        }
        public static void AddParameter(ref SqlCommand cmd, string sParamName, bool bValue)
        {
            SqlParameter param = new SqlParameter(sParamName, bValue);
            param.DbType = DbType.Boolean;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);
        }
        public static void AddParameter(ref SqlCommand cmd, string sParamName, DateTime dtValue)
        {
            SqlParameter param = new SqlParameter(sParamName, dtValue);
            param.DbType = DbType.DateTime;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);
        }
        public static void AddParameter(ref SqlCommand cmd, string sParamName, double dValue)
        {
            SqlParameter param = new SqlParameter(sParamName, dValue);
            param.DbType = DbType.Decimal;
            param.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(param);
        }
        #endregion

        public static SqlCommand getCommand(string CommandText)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = CommandText;
            cmd.Connection = DBHandler.GetConnection();
            return cmd;
        }

        public static DataSet ExecuteSP(SqlCommand command)
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds);

            command.Connection.Close();
            command.Connection.Dispose();

            return ds;
        }

        public static int ExecuteSPNonQuery(SqlCommand command)
        {
            if (command.Connection.State != ConnectionState.Open)
            {
                command.Connection.Open();
            }

            int result = command.ExecuteNonQuery();

            command.Connection.Close();
            command.Connection.Dispose();

            return result;
        }
    }
}